package com.hd.util.report.sqlexcel;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;

import com.hd.dao.JdbcDao;

import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

public class SqlExcelUtil {
	@Autowired
	private JdbcDao jdbcDao;
	/**
	 * 查询出数据后生成excel
	 * @param sql sql语句
	 * @param paramMap sql语句中的参数
	 * @param columnMap 生成Excel列名集合
	 * @param path  生成Excel路径
	 * @param title 生成Excel标题
	 */
	public  void generatorExcelBySql(String sql,Map<String,Object>paramMap,
			Map<String, String> columnMap,String path,String title) {
		List<Map<String, Object>> rows = jdbcDao.findAll(sql, paramMap);
		List<Map<String, Object>> targetList = new ArrayList<Map<String, Object>>();
		if(rows.size()>0) {
			for(Map<String,Object>rowMap:rows) {
				Map<String,Object>map=new LinkedHashMap<>();
				for(Map.Entry<String, String> entry : columnMap.entrySet()) {
					map.put(entry.getValue().toString(), rowMap.get(entry.getKey()));
				}
				targetList.add(map);
			}
		}
		// 通过工具类创建writer
		ExcelWriter writer = ExcelUtil.getWriter(path);
		// 合并单元格后的标题行，使用默认标题样式
		writer.merge(rows.get(0).size() - 1,title);
		// 一次性写出内容，使用默认样式
		writer.write(targetList);
		// 关闭writer，释放内存
		writer.close();
	}
	
	/**
	 * 查询出导出excel表的列
	 * @param fid
	 * @return
	 */
	public  Map<String,String>getColumnMap(String fid){
		String sql="select key_name as keyName,column_name as valueName,status from sql_excel_field where f_id=:fid";
//		if(status!=null&&status==1) {
//			sql+=" and status=1 ";
//		}
		sql+="  order by seq asc";
		Map<String,Object>paramMap=new HashMap<String,Object>();
		Map<String,String>nameMap=new LinkedHashMap<String,String>();
		paramMap.put("fid", fid);
		List<Map<String, Object>> listmap = jdbcDao.findAll(sql, paramMap);
		for(int i=0;i<listmap.size();i++) {
			nameMap.put(listmap.get(i).get("keyName").toString(), listmap.get(i).get("valueName").toString());
		}
		return nameMap;
	}
}
